﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using umbraco.BusinessLogic;
using umbraco.cms.businesslogic.member;

namespace uMember
{
    public class uMembership
    {

        // Example of getting property values out
        // newMember.memberProps["propertynamehere"]

        #region Return Single Members

        /// <summary>
        /// Get a member by Id
        /// </summary>
        /// <param name="memberId"></param>
        /// <returns></returns>
        public uMember GetMemberById(int memberId)
        {
            var newMember = new uMember();

            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QueryGetSingleMemberById, connection);
                selectCommand.Parameters.Add(new SqlParameter("memberId", memberId));
                var rdr = selectCommand.ExecuteReader();

                newMember = MapMember(rdr);
            }
            return newMember;
        }

        /// <summary>
        /// Gets a member by their email address
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public uMember GetMemberByEmail(string email)
        {
            var newMember = new uMember();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QueryGetSingleMemberByEmail, connection);
                selectCommand.Parameters.Add(new SqlParameter("email", email));
                var rdr = selectCommand.ExecuteReader();

                newMember = MapMember(rdr);
            }
            return newMember;
        }

        public uMember GetMemberByLogin(string loginName)
        {
            var newMember = new uMember();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QueryGetSingleMemberByLogin, connection);
                selectCommand.Parameters.Add(new SqlParameter("loginname", loginName));
                var rdr = selectCommand.ExecuteReader();
                newMember = MapMember(rdr);
            }

            return newMember;
        }

        /// <summary>
        /// Returns the current logged in member, null if not logged in
        /// </summary>
        /// <returns></returns>
        public uMember GetCurrentMember()
        {
            var m = Member.GetCurrentMember();
            return m != null ? GetMemberById(m.Id) : null;
        }

        #endregion

        #region Return Multiple Members

        /// <summary>
        /// Return a list of members who registered between a date range
        /// </summary>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <returns></returns>
        public IEnumerable<uMember> SearchMembersByDateCreated(DateTime dateFrom, DateTime dateTo)
        {
            var newMembers = new List<uMember>();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QuerySearchMembersByDateRange, connection);
                var dateFromParameter = new SqlParameter("@dateFrom", SqlDbType.DateTime) { Value = dateFrom};
                var dateToParameter = new SqlParameter("@dateTo", SqlDbType.DateTime) { Value = dateTo };
                selectCommand.Parameters.Add(dateFromParameter);
                selectCommand.Parameters.Add(dateToParameter);

                var rdr = selectCommand.ExecuteReader();

                newMembers = MapMemberLists(rdr);
            }

            return newMembers;
        }

        /// <summary>
        /// Returns list of members from a CSV of member ids
        /// </summary>
        /// <param name="csv"></param>
        /// <returns></returns>
        public IEnumerable<uMember> GetMembersByCSV(string csv)
        {
            if (csv != null)
            {
                // trim any trailing or starting commas just in case
                csv = csv.TrimStart(',').TrimEnd(',');

                var newMembers = new List<uMember>();
                using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
                {
                    connection.Open();
                    var selectCommand = new SqlCommand(uMembershipConstants.QueryGetAllMembersCsv(csv), connection);
                    var rdr = selectCommand.ExecuteReader();

                    newMembers = MapMemberLists(rdr);
                }

                return newMembers;

            }
            return null;
        }

        /// <summary>
        /// Search members by login name
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public IEnumerable<uMember> SearchMembersByLogin(string search)
        {
            var newMembers = new List<uMember>();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QuerySearchMembersByLogin, connection);
                selectCommand.Parameters.AddWithValue("@search", "%" + search + "%");
                var rdr = selectCommand.ExecuteReader();

                newMembers = MapMemberLists(rdr);
            }

            return newMembers;
        }

        /// <summary>
        /// Search for members via a property name and a search value
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="propertyValueToLookFor"></param>
        /// <returns></returns>
        public IEnumerable<uMember> SearchMembersByPropertyValue(string propertyName, string propertyValueToLookFor)
        {
            var newMembers = new List<uMember>();

            //#### Start SQL to get member properties            

            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QuerySearchAllMembersByPropertyValue, connection);
                selectCommand.Parameters.AddWithValue("@propertyName", propertyName);
                selectCommand.Parameters.AddWithValue("@search", "%" + propertyValueToLookFor + "%");
                var rdr = selectCommand.ExecuteReader();

                newMembers = MapMemberLists(rdr);

            }

            return newMembers;
        }

        /// <summary>
        /// Search for members via a property that are all null or not null
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="showNullOnly">True returns members with null as the value of the property, else leave blank and it returns members where the property value is not null</param>
        /// <returns></returns>
        public IEnumerable<uMember> SearchMembersByPropertyValue(string propertyName, bool showNullOnly = false)
        {
            var newMembers = new List<uMember>();

            //#### Start SQL to get member properties            

            var sqlQuery = (showNullOnly) ? uMembershipConstants.QuerySearchAllMembersByPropertyValueIsNull : uMembershipConstants.QuerySearchAllMembersByPropertyValueNotNull;

            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(sqlQuery, connection);
                selectCommand.Parameters.AddWithValue("@propertyName", propertyName);
                var rdr = selectCommand.ExecuteReader();

                newMembers = MapMemberLists(rdr);

            }

            return newMembers;
        }

        /// <summary>
        /// Gets all members
        /// </summary>
        /// <returns></returns>
        public IEnumerable<uMember> GetAllMembers()
        {
            var newMembers = new List<uMember>();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QueryGetAllMembers, connection);
                var rdr = selectCommand.ExecuteReader();
                newMembers = MapMemberLists(rdr);
            }

            return newMembers;
        }

        /// <summary>
        /// Gets all members by their member group, will partial match by name too
        /// </summary>
        /// <param name="groupName"></param>
        /// <returns></returns>
        public IEnumerable<uMember> GetMembersByGroup(string groupName)
        {
            var newMembers = new List<uMember>();
            using (var connection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                connection.Open();
                var selectCommand = new SqlCommand(uMembershipConstants.QueryGetAllMembersByGroupName, connection);
                selectCommand.Parameters.AddWithValue("@group", "%" + groupName + "%");
                var rdr = selectCommand.ExecuteReader();
                newMembers = MapMemberLists(rdr);
            }
            return newMembers;
        }

        #endregion


        #region Mapping

        /// <summary>
        /// Maps a single member
        /// </summary>
        /// <param name="rdr"></param>
        /// <returns></returns>
        private uMember MapMember(IDataReader rdr)
        {
            var newMember = new uMember();

            //#### Start SQL to get member properties            
            var memberProps = new Dictionary<string, string>();

            var i = 0;
            while (rdr.Read())
            {
                if (i == 0)
                {
                    newMember.MemberId = Convert.ToInt32(rdr["nodeId"]);
                    newMember.MemberName = rdr["MemberName"].ToString();
                    newMember.MemberLoginName = rdr["LoginName"].ToString();
                    newMember.MemberEmail = rdr["Email"].ToString();
                    newMember.MemberCreateDateTime = rdr["createDate"].ToString();
                    newMember.MemberGroup = rdr["MemberGroup"].ToString();
                }
                try
                {
                    memberProps.Add(rdr["MemberFieldAlias"].ToString(), rdr["MemberData"].ToString());
                }
                catch (Exception ex)
                {
                    Log.Add(LogTypes.Debug, newMember.MemberId, string.Format("Error getting member {0} property: {1}", newMember.MemberLoginName, ex.Message));
                }
                i++;
            }

            newMember.MemberProperties = memberProps;
            return newMember;
        }

        /// <summary>
        /// Maps multiple members
        /// </summary>
        /// <param name="rdr"></param>
        /// <returns></returns>
        private List<uMember> MapMemberLists(IDataReader rdr)
        {
            var newMembers = new List<uMember>();
            var i = 0;
            var fullCount = 0;
            var totalrows = 0;
            uMember newMember = null;
            Dictionary<string, string> memberProps = null;
            var currentLogin = string.Empty;

            while (rdr.Read())
            {
                if (!string.IsNullOrEmpty(rdr["LoginName"].ToString()))
                {
                    if (fullCount == 0)
                    {
                        // First one so setup everything
                        newMember = new uMember();
                        memberProps = new Dictionary<string, string>();
                        totalrows = Convert.ToInt32(rdr["Count"]);

                    }

                    // Work out if we are now on a new member or not
                    var isNew = currentLogin != rdr["LoginName"].ToString();

                    if (isNew)
                    {
                        // Its a new member
                        if (newMember.MemberLoginName != null)
                        {
                            // Add the previous member to the collection
                            newMember.MemberProperties = memberProps;
                            newMembers.Add(newMember);
                        }

                        // Reset the count
                        i = 0;
                        // Reset the member and member properties
                        newMember = new uMember();
                        memberProps = new Dictionary<string, string>();
                    }

                    // If this is the first time around then add the main data
                    if (i == 0)
                    {
                        newMember.MemberId = Convert.ToInt32(rdr["nodeId"]);
                        newMember.MemberName = rdr["MemberName"].ToString();
                        newMember.MemberLoginName = rdr["LoginName"].ToString();
                        newMember.MemberEmail = rdr["Email"].ToString();
                        newMember.MemberCreateDateTime = rdr["createDate"].ToString();
                        newMember.MemberGroup = rdr["MemberGroup"].ToString();
                    }

                    // Add the member property to the dictionary
                    try
                    {
                        memberProps.Add(rdr["MemberFieldAlias"].ToString(), rdr["MemberData"].ToString());
                    }
                    catch (Exception ex)
                    {
                        Log.Add(LogTypes.Debug, newMember.MemberId, string.Format("Error getting member {0} property: {1}", newMember.MemberLoginName, ex.Message));
                    }

                    // Set the current login so we know when the loop has changed members
                    currentLogin = rdr["LoginName"].ToString();

                    // Increase the count
                    i++;
                    fullCount++;

                    // Bit hacky but add if this is the last record then add the last member
                    if (fullCount == totalrows)
                    {
                        if (newMember.MemberLoginName != null)
                        {
                            // Add the previous member to the collection
                            newMember.MemberProperties = memberProps;
                            newMembers.Add(newMember);
                        }
                    }
                }
            }

            return newMembers.OrderByDescending(x => x.MemberCreateDateTime).ToList();
        } 

        #endregion
    }
}